`:top

`!Transact-SQL`! (`!T-SQL`!) is `F33f`_`[Microsoft`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Microsoft]`_`f's and `F33f`_`[Sybase`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Sybase]`_`f's proprietary extension to the `F33f`_`[SQL`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL]`_`f (Structured Query Language) used to interact with `F33f`_`[relational databases`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Relational_database]`_`f. T-SQL expands on the SQL standard to include `F33f`_`[procedural programming`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Procedural_programming]`_`f, `F33f`_`[local variables`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Local_variable]`_`f, various support functions for string processing, date processing, mathematics, etc. and changes to the `F33f`_`[DELETE`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Delete_(SQL)]`_`f and `F33f`_`[UPDATE`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Update_(SQL)]`_`f statements.

Transact-SQL is central to using `F33f`_`[Microsoft SQL Server`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Microsoft_SQL_Server]`_`f. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

`F33f`_`[Stored procedures`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Stored_procedure]`_`f in SQL Server are executable server-side routines. The advantage of stored procedures is the ability to pass parameters.

>>Contents

• `F0af`_`[Variables`#variables]`_`f

• `F0af`_`[Flow control`#flow-control]`_`f

• `F0af`_`[Changes to DELETE and UPDATE statements`#changes-to-delete-and-update-statements]`_`f

• `F0af`_`[BULK INSERT`#bulk-insert]`_`f

• `F0af`_`[TRY CATCH`#try-catch]`_`f

• `F0af`_`[See also`#see-also]`_`f

• `F0af`_`[References`#references]`_`f

• `F0af`_`[External links`#external-links]`_`f

-─

>>Variables

Transact-SQL provides the following statements to declare and set local variables: `B100`F9d9DECLARE`f`b, `B100`F9d9SET`f`b and `B100`F9d9SELECT`f`b.

`B100`F9d9DECLARE @var1 NVARCHAR(30);`f`b

`B100`F9d9SET @var1 = 'Some Name';`f`b

`B100`F9d9SELECT @var1 = Name`f`b

`B100`F9d9 FROM Sales.Store`f`b

`B100`F9d9 WHERE CustomerID = 100;`f`b

>>Flow control

Keywords for flow control in Transact-SQL include `B100`F9d9BEGIN`f`b and `B100`F9d9END`f`b, `B100`F9d9BREAK`f`b, `B100`F9d9CONTINUE`f`b, `B100`F9d9GOTO`f`b, `B100`F9d9IF`f`b and `B100`F9d9ELSE`f`b, `B100`F9d9RETURN`f`b, `B100`F9d9WAITFOR`f`b, and `B100`F9d9WHILE`f`b.

`B100`F9d9IF`f`b and `B100`F9d9ELSE`f`b allow conditional execution. This batch statement will print "It is the weekend" if the current date is a weekend day, or "It is a weekday" if the current date is a weekday. (Note: This code assumes that Sunday is configured as the first day of the week in the `B100`F9d9@@DATEFIRST`f`b setting.)

`B100`F9d9IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1`f`b

`B100`F9d9 PRINT 'It is the weekend.';`f`b

`B100`F9d9ELSE`f`b

`B100`F9d9 PRINT 'It is a weekday.';`f`b

`B100`F9d9BEGIN`f`b and `B100`F9d9END`f`b mark a `F33f`_`[block of statements`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Block_of_statements]`_`f. If more than one statement is to be controlled by the conditional in the example above, we can use `B100`F9d9BEGIN`f`b and `B100`F9d9END`f`b like this:

`B100`F9d9IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1`f`b

`B100`F9d9BEGIN`f`b

`B100`F9d9 PRINT 'It is the weekend.';`f`b

`B100`F9d9 PRINT 'Get some rest on the weekend!';`f`b

`B100`F9d9END;`f`b

`B100`F9d9ELSE`f`b

`B100`F9d9BEGIN`f`b

`B100`F9d9 PRINT 'It is a weekday.';`f`b

`B100`F9d9 PRINT 'Get to work on a weekday!';`f`b

`B100`F9d9END;`f`b

`B100`F9d9WAITFOR`f`b will wait for a given amount of time, or until a particular time of day. The statement can be used for delays or to block execution until the set time.

`B100`F9d9RETURN`f`b is used to immediately return from a `F33f`_`[stored procedure`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Stored_procedure]`_`f or function.

`B100`F9d9BREAK`f`b ends the enclosing `B100`F9d9WHILE`f`b loop, while `B100`F9d9CONTINUE`f`b causes the next iteration of the loop to execute. An example of a `B100`F9d9WHILE`f`b loop is given below.

`B100`F9d9DECLARE @i INT;`f`b

`B100`F9d9SET @i = 0;`f`b

`B100`F9d9`f`b

`B100`F9d9WHILE @i < 5`f`b

`B100`F9d9BEGIN`f`b

`B100`F9d9 PRINT 'Hello world.';`f`b

`B100`F9d9 SET @i = @i + 1;`f`b

`B100`F9d9END;`f`b

>>Changes to DELETE and UPDATE statements

In Transact-SQL, both the `B100`F9d9DELETE`f`b and `B100`F9d9UPDATE`f`b statements are enhanced to enable data from another table to be used in the operation, without needing a subquery:

• `B100`F9d9DELETE`f`b accepts joined tables in the `B100`F9d9FROM`f`b clause, similarly to `B100`F9d9SELECT`f`b. When this is done, the name or alias of which table in the join is to be deleted from is placed between `B100`F9d9DELETE`f`b and `B100`F9d9FROM`f`b.

• `B100`F9d9UPDATE`f`b allows a `B100`F9d9FROM`f`b clause to be added. The table to be updated can be either joined in the `B100`F9d9FROM`f`b clause and referenced by alias, or referenced only at the start of the statement as per standard SQL.

This example deletes all `B100`F9d9users`f`b who have been flagged in the `B100`F9d9user_flags`f`b table with the 'idle' flag.

`B100`F9d9DELETE u`f`b

`B100`F9d9FROM users AS u INNER JOIN user_flags AS f ON u.id = f.id`f`b

`B100`F9d9WHERE f.name = 'idle';`f`b

>>BULK INSERT

`B100`F9d9BULK`f`b is a Transact-SQL statement that implements a bulk data-loading process, inserting multiple rows into a table, reading data from an external sequential file. Use of `B100`F9d9BULK INSERT`f`b results in better performance than processes that issue individual `B100`F9d9INSERT`f`b statements for each row to be added. Additional details are available in MSDN.

>>TRY CATCH

Beginning with SQL Server 2005,`:cite-ref-1[`F5bf`_`[1`#cite-note-1]`_`f] Microsoft introduced additional `B100`F9d9TRY CATCH`f`b logic to support exception type behaviour. This behaviour enables developers to simplify their code and leave out `B100`F9d9@@ERROR`f`b checking after each SQL execution statement.

`B100`F9d9-- begin transaction`f`b

`B100`F9d9BEGIN TRAN;`f`b

`B100`F9d9`f`b

`B100`F9d9BEGIN TRY`f`b

`B100`F9d9 -- execute each statement`f`b

`B100`F9d9 INSERT INTO MYTABLE(NAME) VALUES ('ABC');`f`b

`B100`F9d9 INSERT INTO MYTABLE(NAME) VALUES ('123');`f`b

`B100`F9d9`f`b

`B100`F9d9 -- commit the transaction`f`b

`B100`F9d9 COMMIT TRAN;`f`b

`B100`F9d9END TRY`f`b

`B100`F9d9BEGIN CATCH`f`b

`B100`F9d9 -- roll back the transaction because of error`f`b

`B100`F9d9 ROLLBACK TRAN;`f`b

`B100`F9d9END CATCH;`f`b

>>See also

• `F33f`_`[Adaptive Server Enterprise (Sybase)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Adaptive_Server_Enterprise]`_`f

• `F33f`_`[PL/SQL (Oracle)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PL/SQL]`_`f

• `F33f`_`[PL/pgSQL (PostgreSQL)`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=PL/pgSQL]`_`f

• `F33f`_`[SQL/PSM`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=SQL/PSM]`_`f (ISO standard)

• `F33f`_`[Tabular Data Stream`:/page/wikibook/entry.mu`zim=wikipedia_en_all_nopic_2025-08.zim|entry_path=Tabular_Data_Stream]`_`f

>>References

`:cite-note-1`!1.`! `F0af`_`[↑`#cite-ref-1]`_`f "T-SQL Improvements in SQL Server 2012", Jonathan Allen on Mar 19, 2012, infoq.com

>>External links

• Transact-SQL Reference

• Transact-SQL Tutorial

`c`F0af`_`[↑ Back to top`#top]`_`f`a